﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using VehicleScale.Models;
using VehicleScale.DataAccess;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using System.IO;
using Janus.Windows.GridEX;
using VehicleScale.Forms.Vehicles;
using MSExcel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Drawing.Printing;

namespace VehicleScale.Reports
{
    public partial class FrmReport : TemplateForm
    {
        #region Singleton Members
        private static volatile FrmReport instance;
        private static object syncRoot = new Object();

        public static FrmReport Instance
        {
            get
            {
                lock (syncRoot)
                {
                    if (instance == null)
                        instance = new FrmReport();
                }

                return instance;
            }
        }

        protected override void OnClosed(EventArgs e)
        {
            instance = null;
            base.OnClosed(e);
        }
        #endregion

        IVehicleScaleDataAccess _dataAccess = new VehicleScaleDataAccess();
        IList<VehicleScaleModel> _vehicleScaleList;

        public FrmReport()
        {
            InitializeComponent();

            this.Size = new Size(395, 390);
            this.rdoCustomer.CheckedChanged += new EventHandler(rdoReportType_CheckedChanged);
            this.rdoProduct.CheckedChanged += new EventHandler(rdoReportType_CheckedChanged);

            LoadCustomersToCombo();
            LoadProductsToCombo();
            LoadCategoryToCombo();


            this.btnSummary.Click += new EventHandler(delegate
            {
                ClickButtonPrint(false);
            });


            this.btnExcel.Click += new EventHandler(delegate
            {
                GridEX gridEX = new GridEX();
                System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(FrmVehicleScaleList));
                Janus.Windows.GridEX.GridEXLayout gridEX_DesignTimeLayout = new Janus.Windows.GridEX.GridEXLayout();
                gridEX_DesignTimeLayout.LayoutString = "<GridEXLayoutData><RootTable><Columns Collection='true' ElementName='Column'><Column0 ID='VehicleScaleID'><AggregateFunction>Count</AggregateFunction><Caption>#MSPC</Caption><DataMember>VehicleScaleID</DataMember><EditType>NoEdit</EditType><Key>VehicleScaleID</Key><Position>0</Position><Visible>False</Visible><Width>102</Width></Column0><Column1 ID='Customer.CustomerName'><Caption>Tên khách hàng</Caption><DataMember>Customer.CustomerName</DataMember><EditType>NoEdit</EditType><Key>Customer.CustomerName</Key><Position>1</Position></Column1><Column2 ID='Customer.Address'><Caption>Địa chỉ</Caption><DataMember>Customer.Address</DataMember><Key>Customer.Address</Key><Position>2</Position></Column2><Column3 ID='Product.ProductName'><Caption>Hàng hóa</Caption><DataMember>Product.ProductName</DataMember><Key>Product.ProductName</Key><Position>3</Position><Width>89</Width></Column3><Column4 ID='ImExType'><Caption>Loại cân</Caption><DataMember>ImExType</DataMember><EditType>NoEdit</EditType><Key>ImExType</Key><Position>4</Position><Visible>False</Visible><Width>93</Width></Column4><Column5 ID='ScaleDate'><Caption>Ngày cân</Caption><DataMember>ScaleDate</DataMember><EditType>NoEdit</EditType><Key>ScaleDate</Key><Position>5</Position><Visible>False</Visible><Width>90</Width></Column5><Column6 ID='TotalFirstWeight'><AggregateFunction>Sum</AggregateFunction><Caption>Tổng lần cân 1</Caption><DataMember>TotalFirstWeight</DataMember><EditType>NoEdit</EditType><FormatString>#,##0</FormatString><Key>TotalFirstWeight</Key><Position>6</Position><Width>106</Width><TotalFormatString>#,##0</TotalFormatString></Column6><Column7 ID='TotalSecondWeight'><AggregateFunction>Sum</AggregateFunction><Caption>Tổng lần cân 2</Caption><DataMember>TotalSecondWeight</DataMember><EditType>NoEdit</EditType><FormatString>#,##0</FormatString><Key>TotalSecondWeight</Key><Position>7</Position><Width>106</Width><TotalFormatString>#,##0</TotalFormatString></Column7><Column8 ID='TotalWeight'><AggregateFunction>Sum</AggregateFunction><Caption>TL Hàng</Caption><DataMember>TotalWeight</DataMember><EditType>NoEdit</EditType><FormatString>#,##0</FormatString><Key>TotalWeight</Key><Position>8</Position><Width>125</Width><TotalFormatString>#,##0</TotalFormatString></Column8><Column9 ID='Employee.EmployeeName'><Caption>Tên nhân viên</Caption><DataMember>Employee.EmployeeName</DataMember><EditType>NoEdit</EditType><Key>Employee.EmployeeName</Key><Position>9</Position><Visible>False</Visible><Width>94</Width></Column9></Columns><GroupCondition /></RootTable></GridEXLayoutData>";
                gridEX.DesignTimeLayout = gridEX_DesignTimeLayout;
                gridEX.BorderStyle = Janus.Windows.GridEX.BorderStyle.Flat;
                gridEX.RowHeaders = Janus.Windows.GridEX.InheritableBoolean.True;
                gridEX.TotalRow = Janus.Windows.GridEX.InheritableBoolean.True;

                BuildContent();
                gridEX.DataSource = _vehicleScaleList;
                int groups = 0;
                if (this.rdoCustomer.Checked)
                {
                    gridEX.RootTable.Groups.Add(gridEX.RootTable.Columns["Customer.CustomerName"]);
                    groups = _vehicleScaleList.GroupBy(v => v.CustomerID).Count();
                }
                else
                {
                    gridEX.RootTable.Groups.Add(gridEX.RootTable.Columns["Product.ProductName"]);
                    groups = _vehicleScaleList.GroupBy(v => v.ProductID).Count();
                }


                gridEX.Refetch();
                int rows = _vehicleScaleList.Count + groups + 2;
                ExportExcel(gridEX, rows);
                try
                {
                    LogFile.WriteLog(LogFile.nameFile, LogFile.userLogin, "Xuat Excel trong Form bao cao");
                }
                catch
                {
                }
            });

            this.btnChart.Click += new EventHandler(delegate
            {
                ClickButtonPrint(true);
            });

            this.btnClose.Click += new EventHandler(delegate
            {
                this.Close();
            });
            try
            {
                LogFile.WriteLog(LogFile.nameFile, LogFile.userLogin, "Mo Form Report");
            }
            catch
            {
            }
        }

        void rdoReportType_CheckedChanged(object sender, EventArgs e)
        {
            if (this.rdoCustomer.Checked)
            {
                cboCustomerID.Enabled = true;
                cboProductID.Enabled = false;
                cboCategory.Enabled =false;
                cboProductID.SelectedIndex = -1;
                cboCategory.SelectedIndex = -1;
            }
            else if (this.rdoProduct.Checked)
            {
                cboProductID.Enabled = true;
                cboCustomerID.Enabled = false;
                cboCategory.Enabled =false;
                cboCustomerID.SelectedIndex = -1;
                cboCategory.SelectedIndex = -1;
            }
            else if (this.rdoCategory.Checked)
            {
                cboCategory.Enabled = true;
                cboCustomerID.Enabled = false;
                cboProductID.Enabled = false;
                cboCustomerID.SelectedIndex = -1;
                cboProductID.SelectedIndex = -1;
                
            }

        }

        /// <summary>
        /// Print Button
        /// </summary>
        private void ClickButtonPrint(bool IsChart)
        {
            BuildContent();
            try
            {
                if (IsChart)
                {
                    if (this.rdoCustomer.Checked)
                    {
                        crChartCustomer report = new crChartCustomer();
                        report.SetDataSource(BuildReport());

                        report.SetParameterValue("ScaleType", rdoTypeAll.Checked ? -1 : (rdoTypeImport.Checked ? 1 : 2));
                        report.SetParameterValue("FromDate", calendarFromDate.Value);
                        report.SetParameterValue("ToDate", calendarToDate.Value);
                        report.SetParameterValue("CompanyName", Common.CompanyInfo.CompanyName);
                        report.SetParameterValue("CompanyAddress", Common.CompanyInfo.Address);
                        
                        if (Common.CheckPrinter(Setting.ReceiptPrintName))
                            report.PrintOptions.PrinterName = Setting.ReportPrintName;
                        if (Setting.IsPreviewReportPrint)
                        {
                            FrmPrintPreview form = new FrmPrintPreview(report);
                            form.ShowDialog();
                        }
                        else
                        {
                            report.PrintToPrinter(1, false, 0, 0);
                        }
                    }
                    else
                    {
                        crChartSummary report = new crChartSummary();
                        report.SetDataSource(BuildReport());

                        report.SetParameterValue("ScaleType", rdoTypeAll.Checked ? -1 : (rdoTypeImport.Checked ? 1 : 2));
                        report.SetParameterValue("FromDate", calendarFromDate.Value);
                        report.SetParameterValue("ToDate", calendarToDate.Value);
                        report.SetParameterValue("CompanyName", Common.CompanyInfo.CompanyName);
                        report.SetParameterValue("CompanyAddress", Common.CompanyInfo.Address);

                        if (Common.CheckPrinter(Setting.ReceiptPrintName))
                            report.PrintOptions.PrinterName = Setting.ReportPrintName;
                        if (Setting.IsPreviewReportPrint)
                        {
                            FrmPrintPreview form = new FrmPrintPreview(report);
                            form.ShowDialog();
                        }
                        else
                        {
                            report.PrintToPrinter(1, false, 0, 0);
                        }
                    }
                }
                else
                {
                    crScaleSummary report = new crScaleSummary();
                    report.SetDataSource(BuildReport());

                    FieldDefinition fieldDef;
                    fieldDef = this.rdoCustomer.Checked ?
                        report.Database.Tables["Customer"].Fields["CustomerName"] : report.Database.Tables["Product"].Fields["ProductName"];
                    report.DataDefinition.Groups[0].ConditionField = fieldDef;

                    report.SetParameterValue("ScaleType", rdoTypeAll.Checked ? -1 : (rdoTypeImport.Checked ? 1 : 2));
                    report.SetParameterValue("FromDate", calendarFromDate.Value);
                    report.SetParameterValue("ToDate", calendarToDate.Value);
                    report.SetParameterValue("CompanyName", Common.CompanyInfo.CompanyName);
                    report.SetParameterValue("CompanyAddress", Common.CompanyInfo.Address);
                    report.SetParameterValue("Note", this.edtNote.Text);

                    if (Common.CheckPrinter(Setting.ReceiptPrintName))
                        report.PrintOptions.PrinterName = Setting.ReportPrintName;
                    if (Setting.IsPreviewReportPrint)
                    {
                        FrmPrintPreview form = new FrmPrintPreview(report);
                        form.ShowDialog();
                    }
                    else
                    {
                        report.PrintToPrinter(1, false, 0, 0);
                    }
                    try
                    {
                        LogFile.WriteLog(LogFile.nameFile, LogFile.userLogin, "Click Thong ke");
                    }
                    catch
                    {
                    }
                }
                // report.PrintToPrinter(1, true, 1, 1);
            }
            catch (CrystalReportsException ex)
            {
                Library.Tools.MsgBox.Show("Lỗi hiển thị trang in. Error details: " + ex.Message, "Alert", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        #region Methods
        private DataSet BuildReport()
        {
            DataSet ds = new DataSet();

            DataTable dataTable = CollectionExtensions<VehicleScaleModel>.ConvertToDataTable(_vehicleScaleList, "VehicleScale");
            ds.Tables.Add(dataTable);

            dataTable = CollectionExtensions<CustomerModel>.ConvertToDataTable(Common.Customers, "Customer");
            ds.Tables.Add(dataTable);

            dataTable = CollectionExtensions<ProductModel>.ConvertToDataTable(Common.Products, "Product");
            ds.Tables.Add(dataTable);

            dataTable = CollectionExtensions<VehicleScaleDetailModel>.ConvertToDataTable(_dataAccess.Details, "VehicleScaleDetail");
            ds.Tables.Add(dataTable);

            ds.Relations.Add("FK_VehicleScale_Customer", ds.Tables["Customer"].Columns["CustomerID"], ds.Tables["VehicleScale"].Columns["CustomerID"]);
            ds.Relations.Add("FK_VehicleScale_Product", ds.Tables["Product"].Columns["ProductID"], ds.Tables["VehicleScale"].Columns["ProductID"]);
            ds.Relations.Add("FK_VehicleScaleDetail_VehicleScale", ds.Tables["VehicleScale"].Columns["VehicleScaleID"], ds.Tables["VehicleScaleDetail"].Columns["VehicleScaleID"]);

            return ds;
        }

        void BuildContent()
        {
            _dataAccess.SearchObject = new VehicleScaleModel();
            if ( this.cboCustomerID.Value != null)
                _dataAccess.SearchObject.CustomerID = this.cboCustomerID.Value.ToString();
            if ( this.cboProductID.Value != null)
                _dataAccess.SearchObject.ProductID = this.cboProductID.Value.ToString();
            if (this.cboCategory.Value != null)
            {
                _dataAccess.SearchObject.CategoryID = this.cboCategory.Value.ToString();
            }
            _dataAccess.SearchObject.ScaleDate = this.calendarFromDate.Value;
            _dataAccess.ToDate = this.calendarToDate.Value;
            if (rdoTypeAll.Checked)
            {
                _dataAccess.SearchObject.ImExType = -1;
            }
            else if (rdoTypeImport.Checked)
            {
                _dataAccess.SearchObject.ImExType = 1;
            }
            else if (rdoTypeExport.Checked)
            {
                _dataAccess.SearchObject.ImExType = 2;
            }

            if (Common.UserLogin != null && Common.UserLogin.GroupPermissionID == 0)
            {
                _dataAccess.SearchObject.EmployeeID = Common.UserLogin.EmployeeID;
            }

            _vehicleScaleList = _dataAccess.Searcher();
        }

        /// <summary>
        /// Load customer list to combo
        /// </summary>
        private void LoadCustomersToCombo()
        {
            this.cboCustomerID.DataSource = Common.Customers;
            this.cboCustomerID.ValueMember = "CustomerID";
            this.cboCustomerID.DisplayMember = "CustomerName";
        }

        /// <summary>
        /// Load product list to combo
        /// </summary>
        private void LoadProductsToCombo()
        {
            this.cboProductID.DataSource = Common.Products;
            this.cboProductID.ValueMember = "ProductID";
            this.cboProductID.DisplayMember = "ProductName";
        }

        private void LoadCategoryToCombo()
        {
            this.cboCategory.DataSource = Common.Categories;
            this.cboCategory.ValueMember = "CategoryID";
            this.cboCategory.DisplayMember = "CategoryName";
        }

        protected void FormatReportExcel(string fileName, int rows)
        {
            MSExcel.Application oXL = null;
            MSExcel.Workbook oWB;
            MSExcel.Worksheet oSheet;
            MSExcel.Range oRng;

            try
            {
                literal.Text = "Đang thực hiện quá trình xuất file Excel. Bạn vui lòng chờ trong giây lát ...";
                Application.DoEvents();

                System.Globalization.CultureInfo oldCultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                //Start Excel and get Application object.
                oXL = new MSExcel.Application();
                oXL.Visible = false;

                //Get a new workbook.
                oWB = oXL.Workbooks.Open(fileName, 0, false, 5, "", "", false, MSExcel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

                if (oWB.Sheets.Count > 0)
                {
                    //string col = Datum.Str(16 + DeliveryDateCollection.Count);
                    //string colNum = Datum.Str(13 + DeliveryDateCollection.Count);
                    oSheet = (MSExcel.Worksheet)oWB.Sheets.get_Item(1);

                    /* Thêm dòng */
                    oRng = oSheet.get_Range("A1", Missing.Value);
                    oRng.EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown, System.Type.Missing);
                    oRng.EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown, System.Type.Missing);
                    oRng.EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown, System.Type.Missing);
                    oRng.EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown, System.Type.Missing);
                    oRng.EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown, System.Type.Missing);
                    oRng.EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown, System.Type.Missing);
                    // Company Name
                    oRng = oSheet.get_Range("A1", "D1");
                    oRng.Merge(true);
                    oRng.Value2 = Common.CompanyInfo.CompanyName;
                    oRng.Font.Bold = true;
                    oRng.Font.Size = 10;
                    oRng.EntireRow.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
                    // Company Address
                    oRng = oSheet.get_Range("A2", "D2");
                    oRng.Merge(true);
                    oRng.Value2 = Common.CompanyInfo.Address;
                    oRng.Font.Bold = true;
                    oRng.Font.Size = 10;
                    oRng.EntireRow.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
                    // Title
                    oRng = oSheet.get_Range("A3", "G3");
                    oRng.Merge(true);
                    oRng.Value2 = "BÁO CÁO CHI TIẾT HÀNG HÓA, NGUYÊN VẬT LIỆU";
                    oRng.Font.Bold = true;
                    oRng.Font.Size = 14;
                    oRng.EntireRow.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
                    // Type
                    oRng = oSheet.get_Range("A4", "G4");
                    oRng.Merge(true);
                    oRng.Value2 = "Hình thức: " +
                        (this.rdoTypeAll.Checked ? "Nhập/Xuất" : (this.rdoTypeImport.Checked ? "Nhập" : "Xuất") + " hàng");
                    oRng.Font.Bold = true;
                    oRng.Font.Size = 10;
                    oRng.EntireRow.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
                    // From To Date
                    oRng = oSheet.get_Range("A5", "G5");
                    oRng.Merge(true);
                    oRng.Value2 = String.Format("Từ ngày {0:dd} tháng {0:MM} năm {0:yyyy} đến ngày {1:dd} tháng {1:MM} năm {1:yyyy}", this.calendarFromDate.Value, this.calendarToDate.Value);
                    oRng.Font.Bold = true;
                    oRng.Font.Size = 10;
                    oRng.EntireRow.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
                    // From To Date
                    oRng = oSheet.get_Range("E7", "E7");
                    oRng.Merge(true);
                    oRng.Value2 = this.rdoTypeAll.Checked ? "TL Lần 1" : this.rdoTypeImport.Checked ? "TL Xe Và Hàng" : "TL Xe";
                    oRng.Font.Size = 10;
                    oRng.EntireRow.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
                    // From To Date
                    oRng = oSheet.get_Range("F7", "F7");
                    oRng.Merge(true);
                    oRng.Value2 = this.rdoTypeAll.Checked ? "TL Lần 2" : this.rdoTypeImport.Checked ? "TL Xe" : "TL Xe Và Hàng";
                    oRng.Font.Size = 10;
                    oRng.EntireRow.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;

                    // Font
                    oRng = oSheet.get_Range("A1", "G" + (rows + 6));
                    oRng.Font.Name = "Times New Roman";

                    // Details
                    oRng = oSheet.get_Range("A7", "G" + (rows + 6));
                    oRng.Font.Size = 10;
                    oRng.Borders.ColorIndex = MSExcel.XlColorIndex.xlColorIndexAutomatic;
                    oRng.Borders.Color = Color.Black.ToArgb();
                    oRng.Borders.LineStyle = MSExcel.XlLineStyle.xlContinuous;

                    // 
                    ///* Format Merge Cells */
                    //oSheet.get_Range("A2", "A3").Merge(false);
                    //oSheet.get_Range("B2", "B3").Merge(false);
                    //oSheet.get_Range("C2", "C3").Merge(false);
                    //oSheet.get_Range("D2", "D3").Merge(false);
                    //oSheet.get_Range("E2", "E3").Merge(false);
                    //oSheet.get_Range("F2", "F3").Merge(false);
                    //oSheet.get_Range("G2", "G3").Merge(false);
                    //oSheet.get_Range("H2", "H3").Merge(false);
                    //oSheet.get_Range("I2", "I3").Merge(false);
                    //oSheet.get_Range("J2", "J3").Merge(false);
                    //oSheet.get_Range("K2", "K3").Merge(false);
                    //oSheet.get_Range("L2", "L3").Merge(false);
                    //oSheet.get_Range("M2", "M3").Merge(false);
                    //oSheet.get_Range(Datum.Str(14 + DeliveryDateCollection.Count) + "2",
                    //    Datum.Str(14 + DeliveryDateCollection.Count) + "3").Merge(false);
                    //oSheet.get_Range(Datum.Str(15 + DeliveryDateCollection.Count) + "2",
                    //    Datum.Str(15 + DeliveryDateCollection.Count) + "3").Merge(false);
                    //oSheet.get_Range(Datum.Str(16 + DeliveryDateCollection.Count) + "2",
                    //    Datum.Str(16 + DeliveryDateCollection.Count) + "3").Merge(false);
                    ///* Format borders */
                    //oRng = oSheet.get_Range("A2", col + (this.gridDataList.RowCount + 4));
                    //oRng.Borders.ColorIndex = MSExcel.XlColorIndex.xlColorIndexAutomatic;
                    //oRng.Borders.Color = Color.Black.ToArgb();
                    //oRng.Borders.LineStyle = MSExcel.XlLineStyle.xlContinuous;
                    ///* Format Checked Headers */
                    //oRng = oSheet.get_Range("I2", "L3");
                    //oRng.Orientation = 90;
                    //oRng = oSheet.get_Range(Datum.Str(14 + DeliveryDateCollection.Count) + "2",
                    //    Datum.Str(14 + DeliveryDateCollection.Count) + "3");
                    //oRng.Orientation = 90;
                    //oRng = oSheet.get_Range("N2", colNum + "2");
                    //oRng.Merge(false);
                    //oRng.Value2 = "Ngày giao";
                    ///* Format Headers */
                    //oRng = oSheet.get_Range("A2", col + "3");
                    //oRng.EntireRow.Font.Bold = true;
                    //oRng.EntireRow.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
                    //oRng.EntireRow.VerticalAlignment = MSExcel.XlVAlign.xlVAlignCenter;
                    //oRng = oSheet.get_Range("A3", col + (this.gridDataList.RowCount + 4));
                    //oRng.EntireRow.AutoFit();
                    //oRng.EntireRow.EntireColumn.AutoFit();
                    ///* Format Numbers. */
                    //oRng = oSheet.get_Range("E4", Missing.Value);
                    //oRng.EntireColumn.NumberFormat = "#,##0";
                    //oRng = oSheet.get_Range("G4", Missing.Value);
                    //oRng.EntireColumn.NumberFormat = "#,##0";
                    //oRng = oSheet.get_Range("N4", colNum + "4");
                    //oRng.EntireColumn.NumberFormat = "#,##0";
                    //oRng = null;
                    /* Auto Fit */
                    oRng = oSheet.get_Range("A7", "G7");
                    oRng.EntireColumn.AutoFit();
                    ///* Format Print pages. */
                    oSheet.PageSetup.Orientation = MSExcel.XlPageOrientation.xlPortrait;
                    oSheet.PageSetup.PrintTitleRows = "$2:$3";
                    oSheet.PageSetup.RightMargin = 0;
                    oSheet.PageSetup.LeftMargin = 0;
                    oSheet.PageSetup.BottomMargin = 0;
                    oSheet.PageSetup.TopMargin = 0;
                    oSheet.PageSetup.CenterHorizontally = true;
                    oSheet.PageSetup.HeaderMargin = 0;
                    oSheet.PageSetup.FooterMargin = 0;
                    oSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
                    oSheet.PageSetup.RightFooter = "- &P -";
                    //oSheet.PageSetup.Zoom = false;
                    //oSheet.PageSetup.FitToPagesTall = 1;
                    //oSheet.PageSetup.FitToPagesWide = 1;
                    oSheet = null;
                    oWB.Save();
                    oWB.Close(false, "", Type.Missing);
                }
                oWB = null;

                oXL.Visible = true;
                oXL.UserControl = true;

                oXL.Quit();
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCultureInfo;
                literal.Text = String.Empty;
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                MessageBox.Show(errorMessage, "Error");
            }
        }

        #region Methods
        private bool ExportExcel(GridEX gridEx, int rows)
        {
            bool result = false;
            try
            {
                this.gridEXExporter1.GridEX = gridEx;
                gridEXExporter1.ExportMode = ExportMode.AllRows;
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "All Microsoft Excel files|*.xls|All Microsoft Excel 2007 files|*.xlsx|All Files|*.*";
                saveFileDialog.DefaultExt = "*.xls";
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    if (File.Exists(saveFileDialog.FileName))
                        File.Delete(saveFileDialog.FileName);
                    using (Stream stream = new FileStream(saveFileDialog.FileName, System.IO.FileMode.CreateNew))
                    {
                        this.gridEXExporter1.Export(stream);
                    }
                    gridEx.RetrieveStructure();

                    this.FormatReportExcel(saveFileDialog.FileName, rows);
                    DialogResult result1;
                    result1 = Library.Tools.MsgBox.Show("Quá trình xuất file Excel (" + saveFileDialog.FileName + ") thành công. Bạn có muốn mở file?", "Câu hỏi", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    if (result1 == DialogResult.OK)
                        System.Diagnostics.Process.Start(saveFileDialog.FileName);
                    result = true;
                }
            }
            catch (Exception ex)
            {
                Library.Tools.MsgBox.Show("Quá trình xuất file Excel bị lỗi: " + ex.Message, "Cảnh báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            return result;
        }
        #endregion

      

       

       

        #endregion
    }
}
